package googleMalURLFinder;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import uk.co.mccann.gsb.engine.GoogleSafeBrowser;
import uk.co.mccann.gsb.exceptions.GSBException;
import uk.co.mccann.gsb.interfaces.GSBDAO;
import uk.co.mccann.gsb.interfaces.GSBEngine;
import uk.co.mccann.gsb.model.FileDAO;
import Utility.*;
public class googleMalURLFinder {
	static GSBEngine gsb;
	static GSBDAO fileDao;
	static int MAX_URL_BATCH = 100;
	googleMalURLFinder()
	{
		try
		{
			gsb = new GoogleSafeBrowser("ABQIAAAAlESu76xcrubTKKgj_L_r7xTtAO9UKrRyqIhi_dnWJXj1pg7bPg", "/home/bobby/gsb/", false);
			 
			 /* Use a flat file to read /store data (will create the files in the same directory as your preferences set in the session)*/
			fileDao = new FileDAO(); 
			 
			 /* Use MySQL Data Access Object to read / store data */
			//fileDao = new MySQLDAO("username", "password", "localhost", "your_db_name");
			 
			 /* Register MySQL DAO with GoogleSafeBrowser */
			gsb.registerDAO(fileDao);
		}
		catch(GSBException gsbe)
		{
			Utility.logMessage(gsbe.getMessage(), "googleMalURLFinder_error.txt");
			System.exit(1);
		}
	}
	public static void update_blacklists()
	{
		try
		{
			/* Update blacklist and malwarelist (if you have never run this before, all data will be downloaded) */
			gsb.updateMalwarelist();
			gsb.updateBlacklist();
		}
		catch(GSBException gsbe)
		{
			Utility.logMessage(gsbe.getMessage(), "googleMalURLFinder_warning.txt");
		}
	}
	
	public static void analyzeURLs(Connection conn, int start, int end)
	{
		try
		{
			Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					 ResultSet.CONCUR_READ_ONLY);
			
			ResultSet srs = stmt.executeQuery("SELECT id, url FROM URLMap where id >= " + start + 
																		" AND id < " + end + " AND google_status = '?'");
			PreparedStatement pstmt = conn.prepareStatement("UPDATE urls SET google_status = ? WHERE id = ?");
			int id;
			
			while(srs.next())
			{
				String url = srs.getString("url");
				try
				{
					//System.out.println(text);
					id = srs.getInt("id");
					if(gsb.isDangerous(url)) 
					{
						pstmt.setString(1, "M");
					}
					else
					{
						pstmt.setString(1, "N");
					}
					pstmt.setInt(2,id);
					pstmt.execute();
				}
				catch (GSBException e) 
				{
					//e.printStackTrace();
					Utility.logMessage(e.getMessage() + " for " + url, "googleMalURLFinder_error.txt");			
				}
			}
		}
		catch(SQLException sqle)
		{
			Utility.logMessage(sqle.getMessage(), "googleMalURLFinder_error.txt");
		}
	}
	
	public static void main(String args[])
	{
		/* test if URL is dangerous or not! */
		try 
		{	
			 
			Connection conn = null;
			Class.forName("org.sqlite.JDBC");
		    conn = DriverManager.getConnection("jdbc:sqlite:urls_copy.db");
			
			Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                    							 ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = stmt.executeQuery("SELECT id FROM urls WHERE google_status = '?' LIMIT 1");
			rs.next();		
			int next_id = 0;
			if(!rs.isAfterLast())
			{
				next_id = rs.getInt("id");
			}
			rs.close();
			
			rs = stmt.executeQuery("SELECT id FROM urls WHERE google_status = '?' ORDER BY id DESC LIMIT 1");
			rs.next();		
			int last_id = 0;
			if(!rs.isAfterLast())
			{
				last_id = rs.getInt("id");
			}
			rs.close();
			
			Utility.logMessage("Start id: " + next_id + "Last id: " + last_id, "googleMalURLFinder_log.txt");
			
			while(true)
			{
				if(next_id >= last_id)
				{
					analyzeURLs(conn, next_id, last_id);
					break;
				}
				else
				{
					analyzeURLs(conn, next_id, next_id + MAX_URL_BATCH);
					next_id += MAX_URL_BATCH;
				}
			} 
		} 		
		catch(Exception e) 
		{
			e.printStackTrace();
		}
	}	 
}
